﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace OneShoppingDAL
{

    public class SqlUtility
    {

        /// <summary>
        ///  定义静态的连接字符串
        /// </summary>
        private string ConnString
        {
            get
            {
                return System.Configuration.ConfigurationManager.
                    ConnectionStrings["OneShoppingModel.Properties.Settings.oneshoppingConnectionString"].ConnectionString;

            }
        }

        private static SqlUtility instance;

        public static SqlUtility GetInstance()
        {
            if (instance == null)
            {
                instance = new SqlUtility();
            }
            return instance;
        }


        /// <summary>
        /// 关闭连接对象
        /// </summary>
        /// <param name="conn">需关闭的连接对象</param>
        private void CloseConn(SqlConnection conn)
        {
            if (conn != null)
            {
                conn.Close();
            }
        }



        /// <summary>
        /// 执行一个insert/update/delete语句或则相应的存储过程
        /// </summary>
        /// <param name="cmdText">insert/update/delete SQL语句或则 存储过程</param>
        /// <param name="type">指定命令的类型</param>
        /// <param name="pars">参数集合</param>
        /// <returns>int</returns>
        public int ExcuteSqlReturnInt(string cmdText, CommandType type, params SqlParameter[] pars)
        {

            //实例化连接对象
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                //打开连接对象
                if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
                {
                    conn.Open();
                }
                //实例化命令对象
                SqlCommand sqlcommand = new SqlCommand(cmdText, conn);
                //对命令对象参数集合赋值       
                if (pars != null && pars.Length > 0)
                {
                    foreach (SqlParameter p in pars)
                    {
                        sqlcommand.Parameters.Add(p);
                    }
                }
                //给命令对象指定类型
                sqlcommand.CommandType = type;
                //通过ExecuteNonQuery执行数据库命令，并返回数据库受影响的行数。
                int count = sqlcommand.ExecuteNonQuery();

                return count;
            }
            catch (Exception ex)
            {
                return 0;
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConn(conn);

            }
        }

        /// <summary>
        /// 执行一个Select语句或则相关的存储过程 并返回DataSet
        /// </summary>
        /// <param name="cmdText">Select语句或则相关的存储过程</param>
        /// <param name="type">命令对象类型</param>
        /// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
        /// <returns>DataSet</returns>
        private DataSet ExcuteSqlReturnDataSet(string cmdText, CommandType type, params SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmdText, conn);
                if (pars != null && pars.Length > 0)
                {
                    foreach (SqlParameter p in pars)
                    {
                        sqldataadapter.SelectCommand.Parameters.Add(p);
                    }
                }
                sqldataadapter.SelectCommand.CommandType = type;
                DataSet dt = new DataSet();
                sqldataadapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConn(conn);

            }

        }

        /// <summary>
        /// 执行一个Select语句或则相关的存储过程 并返回DataSet
        /// </summary>
        /// <param name="cmdText">Select语句或则相关的存储过程</param>
        /// <param name="type">命令对象类型</param>
        /// <returns>DataSet</returns>
        private DataSet ExcuteSqlReturnDataSet(string cmdText, CommandType type)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmdText, conn);
                sqldataadapter.SelectCommand.CommandType = type;
                DataSet dt = new DataSet();
                sqldataadapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConn(conn);

            }

        }




        /// <summary>
        /// 执行一个Select语句或则相关的存储过程 并返回DataTable
        /// </summary>
        /// <param name="cmdText">Select语句或则相关的存储过程</param>
        /// <param name="type">命令对象类型</param>
        /// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
        /// <returns>DataTable</returns>
        private DataTable ExcuteSqlReturnDataTable(string cmdText, CommandType type, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                SqlDataAdapter sqldataadatapter = new SqlDataAdapter(cmdText, conn);
                if (pars != null && pars.Length > 0)
                {
                    foreach (SqlParameter p in pars)
                    {
                        sqldataadatapter.SelectCommand.Parameters.Add(p);
                    }
                }
                sqldataadatapter.SelectCommand.CommandType = type;
                DataTable dt = new DataTable();
                sqldataadatapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConn(conn);

            }
        }


        /// <summary>
        /// 执行一个Select语句或则相关的存储过程 并返回DataTable
        /// </summary>
        /// <param name="cmdText">Select语句或则相关的存储过程</param>
        /// <param name="type">命令对象类型</param>
        /// <returns>DataTable</returns>
        private DataTable ExcuteSqlReturnDataTable(string cmdText, CommandType type)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                SqlDataAdapter sqldataadatapter = new SqlDataAdapter(cmdText, conn);
                sqldataadatapter.SelectCommand.CommandType = type;
                DataTable dt = new DataTable();
                sqldataadatapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConn(conn);

            }
        }

        /// <summary>
        /// 执行一个Select语句或则相关的存储过程 并返回查询对象,通常用于聚合操作
        /// </summary>
        /// <param name="cmdText">Select语句或则相关的存储过程</param>
        /// <param name="type">命令对象类型</param>
        /// <param name="pars">Select语句或则相关的存储过程 所需的参数</param>
        /// <returns>object</returns>
        /// 

        private object ExcuteSqlReturnObject(string cmdText, CommandType type, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                foreach (SqlParameter p in pars)
                {
                    cmd.Parameters.Add(p);
                }
                if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
                {
                    conn.Open();
                }
                cmd.CommandType = type;
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConn(conn);
            }

        }

        /// <summary>
        /// 执行一个Select语句或则相关的存储过程 并返回一个数据阅读器对象
        /// </summary>
        /// <param name="cmdText">Select语句或则相关的存储过程</param>
        /// <param name="type">指定命令对象的类型</param>
        /// <param name="pars">参数结合</param>
        /// <returns>SqlDataReader</returns>
        private SqlDataReader ExcuteSqlReturnReader(string cmdText, CommandType type, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            try
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                if (pars != null && pars.Length > 0)
                {
                    foreach (SqlParameter p in pars)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                cmd.CommandType = type;
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//关闭数据阅读器时会自动关闭连接对象
                return reader;
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConn(conn);
            }
        }






        /// <summary>
        /// 通用数据插入方法(适用主键列自增长的表)，示例：CommonInsert("User", "Username,DepartmentId", "'张三',6")
        /// </summary>
        /// <param name="tableName">要添加数据的表</param>
        /// <param name="columns">列名间隔集，列间逗号隔开，null则添加的数据所有列</param>
        /// <param name="columnvalues">添加的数据的值，与列名集合一一对应</param>
        /// <returns>添加是否成功</returns>
        public bool CommonInsert(string tableName, string columns, string columnvalues)
        {
            SqlParameter[] pars = new SqlParameter[]{
             new SqlParameter("@tableName",tableName),
             new SqlParameter("@columns",columns),
             new SqlParameter("@columnvalues",columnvalues)   
            };
            int count = ExcuteSqlReturnInt("CommonInsert", CommandType.StoredProcedure, pars);
            return (count > 0);
        }


        /// <summary>
        /// 通用删除方法,示例：CommonDelete("Department", "DepartmentId=6")
        /// </summary>
        /// <param name="tableName">要删除数据的表</param>
        /// <param name="condition">删除条件,null则删除整张表数据</param>
        /// <returns>删除是否成功</returns>
        public bool CommonDelete(string tableName, string condition)
        {
            SqlParameter[] pars = new SqlParameter[]{
            new SqlParameter("@tableName",tableName),
            new SqlParameter("@condition",condition)
            };
            int count = ExcuteSqlReturnInt("CommonDelete", CommandType.StoredProcedure, pars);
            return (count > 0);
        }


        /// <summary>
        /// 通用的修改方法，示例：CommonUpdate("User", "Username='张三',DepartmentId=2","UserId=126")
        /// </summary>
        /// <param name="tableName">修改的表名</param>
        /// <param name="columns">要修改的列及修改的值 </param>
        /// <param name="conditions">修改的条件</param>
        /// <returns></returns>
        public bool CommonUpdate(string tableName, string columns, string conditions)
        {
            SqlParameter[] pars = new SqlParameter[]{
            new SqlParameter("@tableName",tableName),
            new SqlParameter("@columns",columns),
            new SqlParameter("@conditions",conditions)
            };
            int count = ExcuteSqlReturnInt("CommonUpdate", CommandType.StoredProcedure, pars);
            return (count > 0);
        }






        /// <summary>
        /// 通用的按条件查询数据表的方法,示例：CommonSelectDataByCondition("User", "UserName,LoginPwd,LassLoginIP","DepartmentId=6")
        /// </summary>
        /// <param name="tableName">指定的表或视图名</param>
        /// <param name="columns">指定列名,null或*表示查询所有列</param>
        /// <param name="condition">指定的条件，null则查询整张表</param>
        /// <returns>DataSet</returns>
        public DataTable CommonSelectDataByCondition(string tableName, string columns, string condition)
        {
            SqlParameter[] pars = new SqlParameter[]{
            new SqlParameter("@tableName",tableName),
            new SqlParameter("@columns",columns),
            new SqlParameter("@condition",condition)
            };
            return ExcuteSqlReturnDataTable("CommonSelectDataByCondition", CommandType.StoredProcedure, pars);
        }



        /// <summary>
        /// 通用的分页查询数据的方法，示例：CommonSelectDataByPageIndex("User", "UserID,UserName,Phone","UserID","and DepartmentId=6",0,5)
        /// </summary>
        /// <param name="table">查询的表或视图</param>
        /// <param name="columns">查询的列，null或*查询整表</param>
        /// <param name="pk">查询的表的主键</param>
        /// <param name="condition">查询条件，约定每个查询条件(包括第一个条件) 前均需加上and</param>
        /// <param name="pageindex">页码，默认第0页</param>
        /// <param name="pagesize">页面大小，默认每页10条</param>
        /// <returns></returns>
        public DataTable CommonSelectDataByPageIndex(string table, string columns, string pk, string condition,
             int? pageindex, int? pagesize)
        {
            SqlParameter[] pars = new SqlParameter[]{
                new SqlParameter("@tableName",table),
                new SqlParameter("@columns",columns),
                new SqlParameter("@pk",pk),
                new SqlParameter("@condition",condition),
                new SqlParameter("@pageindex",pageindex),
                new SqlParameter("@pagesize",pagesize)
            };
            return ExcuteSqlReturnDataTable("CommonSelectDataByPageIndex", CommandType.StoredProcedure, pars);
        }
        /// <summary>
        /// 通用的查询数据记录数的方法,示例：CommonSelectDataCountByConditione("User", "and DepartmentId=6")
        /// </summary>
        /// <param name="table">查询的表或视图</param>
        /// <param name="condition">查询条件，null为全表</param>
        /// <returns></returns>
        public int CommonSelectDataCountByCondition(string table, string condition)
        {
            SqlParameter[] pars = new SqlParameter[] {
            new SqlParameter("@tableName",table),
            new SqlParameter("@condition",condition)
            };
            Object obj = ExcuteSqlReturnObject("CommonSelectDataCountByCondition",
                CommandType.StoredProcedure, pars);
            return (obj != null) ? (Convert.ToInt32(obj)) : 0;
        }




        /// <summary>
        /// 通用的获取最大的主键Id值的方法,示例：CommonSelectMaxIdByCondition("Device","DeviceID",null)
        /// </summary>
        /// <param name="table">查询的表或视图</param>
        /// <param name="pk">主键</param>
        /// <param name="condition">查询条件，null查询整表</param>
        /// <returns></returns>
        public int CommonSelectMaxIdByCondition(string table, string pk, string condition)
        {
            SqlParameter[] pars = new SqlParameter[] {
            new SqlParameter("@tableName",table),
            new SqlParameter("@pk",pk),
            new SqlParameter("@condition",condition)
            };
            Object obj = ExcuteSqlReturnObject("CommonSelectMaxIdByCondition",
                CommandType.StoredProcedure, pars);
            return (obj != null) ? (Convert.ToInt32(obj)) : 0;
        }

      
    }
}
